package com.xiaoma.verbal.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.xiaoma.verbal.baseDao.BaseDaoImpl;
import com.xiaoma.verbal.baseDao.DefaultRowMapper;
import com.xiaoma.tpo.utils.Pager;
import com.xiaoma.verbal.domain.QuQuestion;
import com.xiaoma.verbal.domain.QuTpo;
import com.xiaoma.verbal.dto.QuTpoVo;
import com.xiaoma.verbal.dto.QuestionVo;

@Repository
public class QuTpoDao extends BaseDaoImpl<QuTpo> {
	private static final Logger LOG = LoggerFactory.getLogger(QuTpoDao.class);

	@SuppressWarnings("unchecked")
	public List<Integer> findTpoTasks() {
		String sql = "SELECT a.task_num FROM qu_tpo a left join qu_question b on a.question_id = b.id  where b.question_type = 3 group by a.task_num  ";
		LOG.info("SQL:{} PARAMS:{}", sql);
		List<?> result = jdbcTemplate.query(sql, new RowMapper() {

			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				return rs.getInt("task_num");
			}

		});
		return (List<Integer>) result;
	}

	public List<QuTpoVo> findTpoTaskQuestions(Integer taskNum) {
		String sql = "SELECT a.uuid,a.task_num,a.tpo_num,b.* FROM qu_tpo a left join qu_question b on a.question_id = b.id  where b.question_type = 3 and a.task_num = ? order by a.tpo_num asc";
		LOG.info("SQL:{} PARAMS:{}", sql, taskNum);
		List<?> result = jdbcTemplate.query(sql, new DefaultRowMapper(QuTpoVo.class, getNameHandler()), taskNum);
		return (List<QuTpoVo>) result;
	}

	public Long getTimeCoordinate() {
		String sql = "SELECT sum(a.update_time) sumTime1, sum(b.update_time) sumTime2 FROM qu_tpo a left join qu_question b on a.question_id = b.id  where b.question_type = 3 and (a.task_num=1 or a.task_num =2)";
		LOG.info(sql + " PARAMS: ");
		Long result = jdbcTemplate.queryForObject(sql, new RowMapper() {
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				return rs.getLong("sumTime1")+rs.getLong("sumTime2");
			}
		});
		return result;
	}

	public Pager queryPageList(Integer id, String qnum, Integer taskNum, Integer tpoNum, String tagText, String content, Integer startIndex, Integer itemsPerPage) {
		Pager pager = new Pager();
		List<Object> params = new ArrayList<Object>();
		String sqlQuery = " ";
		String sqlCount = " ";
		sqlQuery = "select a.tpo_num,a.task_num,b.* ";
		String sqlTrunk = " from qu_tpo  a  left join qu_question b on a.question_id = b.id ";
		String where = " where b.question_type = 3 ";
		String orderBy = " order by a.tpo_num,a.task_num desc ";
		String limit = " limit ?,? ";
		String groupBy = " group by a.id ";
		if (id != null && !"".equals(id)) {
			where = where + " and b.id = ? ";
			params.add(id);
		}
		if (qnum != null && !"".equals(qnum)) {
			where = where + " and b.question_num = upper(?) ";
			params.add(qnum.toUpperCase());
		}
		if (taskNum != null && !"".equals(taskNum)) {
			where = where + " and a.task_num = ? ";
			params.add(taskNum);
		}
		if (tpoNum != null && !"".equals(tpoNum)) {
			where = where + " and a.tpo_num = ? ";
			params.add(tpoNum);
		}
		if (content != null && !"".equals(content)) {
			where = where + " and b.content like ? ";
			params.add("%" + content + "%");
		}

		if (tagText != null && !"".equals(tagText)) {
			where = where + " and c.tag_text like ? ";
			params.add("%" + tagText + "%");
		}

		sqlQuery = sqlQuery + sqlTrunk + where + orderBy + limit;
		sqlCount = "select count(*)  " + sqlTrunk + where;
		LOG.info(sqlQuery + " PARAMS: " + startIndex + ", " + itemsPerPage);
		LOG.info(sqlCount);
		int totalCount = jdbcTemplate.queryForInt(sqlCount, params.toArray());

		params.add(startIndex);
		params.add(itemsPerPage);
		List<?> list = jdbcTemplate.query(sqlQuery, params.toArray(), new DefaultRowMapper(QuTpoVo.class, this.getNameHandler()));

		pager.setList(list);
		pager.setTotalCount(totalCount);
		return pager;
	}

	public QuTpo findByQnum(String qNum) {
		// TODO Auto-generated method stub
		String sqlQuery = "select * from qu_tpo a  left join qu_question b on a.question_id = b.id  where b.question_type = 3 and b.question_num = ? limit 1";
		List<?> list = jdbcTemplate.query(sqlQuery, new DefaultRowMapper(QuTpo.class, this.getNameHandler()), qNum);
		if (list != null && list.size() > 0) {
			return (QuTpo) list.get(0);
		}
		return null;
	}
	/**
	 * 
	 *新的 
	 * 
	 */
	public List<QuTpoVo> findTpoTaskQuestionsNew(Integer taskNum) {
		String sql = "SELECT a.uuid,a.task_num,a.tpo_num,b.* FROM "
				+ " qu_tpo a left join "
				+ " qu_question b "
				+ " on a.question_id = b.id  "
				+ " where b.question_type = 3 and a.task_num = ? order by a.tpo_num asc";
		LOG.info("SQL:{} PARAMS:{}", sql, taskNum);
		List<?> result = jdbcTemplate.query(sql, new DefaultRowMapper(QuTpoVo.class, getNameHandler()), taskNum);
		return (List<QuTpoVo>) result;
	}
}
